/**
 * The database package of the application.
 */
package fr.umlv.m2.android.quizz.database;

import java.sql.Time;
import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

/**
 * This class defines the database score object.
 * 
 * @author Gambouele Bertrand.
 * @author Viravout Alexandre.
 */
public class ScoreDb {
	/**
	 * The score single player mode index.
	 */
	public static final int SINGLE_PLAYER = 0;
	/**
	 * The score multiplayer mode index.
	 */
	public static final int MULTIPLAYER = 1;
	
	/**
	 * The database version.
	 */
	private static final int DB_VERSION = 1;
	/**
	 * The database name.
	 */
	private static final String DB_NAME = "quizz.db";

	/**
	 * The score table name.
	 */
	private static final String SCORE_TABLE = "score";
	/**
	 * The score id column name.
	 */
	private static final String ID = "id";
	/**
	 * The score id column number.
	 */
	private static final int ID_COL_NUM = 0;
	/**
	 * The score tournament rank column name.
	 */
	private static final String TOURNAMENT_RANK = "tournamentRank";
	/**
	 * The score tournament rank column number.
	 */
	private static final int TOURNAMENT_RANK_COL_NUM = 1;
	/**
	 * The score good answer column name.
	 */
	private static final String GOOD_ANSWER = "goodAnswer";
	/**
	 * The score good answer column number.
	 */
	private static final int GOOD_ANSWER_COL_NUM = 2;
	/**
	 * The score bad answer column name.
	 */
	private static final String BAD_ANSWER = "badAnswer";
	/**
	 * The score bad answer column number.
	 */
	private static final int BAD_ANSWER_COL_NUM = 3;
	/**
	 * The score percentage of success column name.
	 */
	private static final String SUCCESS_PERCENTAGE = "successPercentage";
	/**
	 * The score percentage of success column number.
	 */
	private static final int SUCCESS_PERCENTAGE_COL_NUM = 4;
	/**
	 * The score time column name.
	 */
	private static final String TIME = "time";
	/**
	 * The score time column number.
	 */
	private static final int TIME_COL_NUM = 5;
	/**
	 * The score user id column name.
	 */
	private static final String USER_ID = "userId";
	/**
	 * The score user id column number.
	 */
	private static final int USER_ID_COL_NUM = 6;
	/**
	 * The score level id column name.
	 */
	private static final String LEVEL_ID = "levelId";
	/**
	 * The score level id column number.
	 */
	private static final int LEVEL_ID_COL_NUM = 7;
	/**
	 * The score theme id column name.
	 */
	private static final String THEME_ID = "themeId";
	/**
	 * The score theme id column number.
	 */
	private static final int THEME_ID_COL_NUM = 8;

	/**
	 * The database of the application.
	 */
	private SQLiteDatabase db;
	/**
	 * The database open helper.
	 */
	private final QuizzDatabaseOpenHelper quizzSQLite;
	
	/**
	 * Create the database score object.
	 *  
	 * @param context the context.
	 */
	public ScoreDb(Context context) {
		this.quizzSQLite = new QuizzDatabaseOpenHelper(context, DB_NAME, null, DB_VERSION);
	}

	/**
	 * Opens the database.
	 */
	public void open() {
		this.db = this.quizzSQLite.getWritableDatabase();
	}

	/**
	 * Closes the database.
	 */
	public void close() {
		this.db.close();
	}

	/**
	 * Gets the database.
	 * 
	 * @return the database.
	 */
	public SQLiteDatabase getDb() {
		return this.db;
	}
	
	/**
	 * Gets the number of score.
	 * 
	 * @return the number of score.
	 */
	public int getNbScore(long levelId, long themeId) {		
		Cursor cursor = this.db.query(
				SCORE_TABLE, 
				new String[] { ID }, 
				LEVEL_ID + " = " + levelId + " and " + THEME_ID + " = " + themeId,
				null, null, null, null
		);
		int count = cursor.getCount();
		cursor.close();
		
		return count;
	}
	
	/**
	 * Clear the score table.
	 */
	public void clear() {
		this.quizzSQLite.clearTable(this.db, SCORE_TABLE);
	}

	/**
	 * Inserts a score in the database.
	 * 
	 * @param score the score to insert.
	 * @return the row ID of the newly inserted row, or -1 if an error occurred.
	 */
	public long insertScore(Score score) {
		ContentValues values = new ContentValues();

		values.put(GOOD_ANSWER, score.getGoodAnswer());
		values.put(BAD_ANSWER, score.getBadAnswer());
		values.put(SUCCESS_PERCENTAGE, score.getSuccessPercentage());
		values.put(USER_ID, score.getUserId());
		values.put(LEVEL_ID, score.getLevelId());
		values.put(THEME_ID, score.getThemeId());
		
		if(score.getTournamentRank() > -1) {	
			values.put(TOURNAMENT_RANK, score.getTournamentRank());
		} else if(score.getTime() != new Time(-1).getTime()) {
			values.put(TIME, score.getTime());
		}

		return this.db.insert(SCORE_TABLE, null, values);
	}
	
	private List<Score> cursorToScores(Cursor cursor, int gameMode) {
		List<Score> scores = new ArrayList<Score>();
		if(cursor.getCount() == 0) {
			return null;
		} else {
			cursor.moveToFirst();
		}
		
		Score score;
		do {
			score = new Score();
			score.setId(cursor.getInt(ID_COL_NUM));
			score.setGoodAnswer(cursor.getInt(GOOD_ANSWER_COL_NUM));
			score.setBadAnswer(cursor.getInt(BAD_ANSWER_COL_NUM));
			score.setSuccessPercentage(cursor.getInt(SUCCESS_PERCENTAGE_COL_NUM));
			score.setUserId(cursor.getInt(USER_ID_COL_NUM));
			score.setLevelId(cursor.getInt(LEVEL_ID_COL_NUM));
			score.setThemeId(cursor.getInt(THEME_ID_COL_NUM));
			
			if(gameMode == SINGLE_PLAYER) {
				score.setTime(cursor.getLong(TIME_COL_NUM));
			} else if(gameMode == MULTIPLAYER) {
				score.setTournamentRank(cursor.getInt(TOURNAMENT_RANK_COL_NUM));
			}
			
			scores.add(score);
		} while(cursor.moveToNext());
		cursor.close();
		
		return scores;
	}
	
	/**
	 * Gets the single player mode scores according level id and theme id.
	 * 
	 * @param levelId the level id.
	 * @param themeId the theme id.
	 * @return the list of score.
	 */
	public List<Score> getSinglePlayerScores(long levelId, long themeId) {
		Cursor cursor = this.db.query(
				SCORE_TABLE,
				new String[] {
						ID,
						TOURNAMENT_RANK,
						GOOD_ANSWER,
						BAD_ANSWER,
						SUCCESS_PERCENTAGE,
						TIME,
						USER_ID,
						LEVEL_ID,
						THEME_ID
				},
				LEVEL_ID + " = " + levelId + " and " + THEME_ID + " = " + themeId,
				null, null, null,
				GOOD_ANSWER + " desc, " + TIME + ", " + ID
		);
		
		return cursorToScores(cursor, SINGLE_PLAYER);
	}
	
	public List<Score> getMultiplayerScores(long levelId, long themeId) {
		Cursor cursor = this.db.query(
				SCORE_TABLE,
				new String[] {
						ID,
						TOURNAMENT_RANK,
						GOOD_ANSWER,
						BAD_ANSWER,
						SUCCESS_PERCENTAGE,
						TIME,
						USER_ID,
						LEVEL_ID,
						THEME_ID
				},
				LEVEL_ID + " = " + levelId + " and " + THEME_ID + " = " + themeId,
				null, null, null,
				ID
		);
		
		return cursorToScores(cursor, MULTIPLAYER);
	}
 
	/**
	 * Updates score data according his id.
	 * 
	 * @param id the score id.
	 * @param score the new score object.
	 * @return the number of rows affected.
	 */
	public int updateScore(long id, Score score) {
		ContentValues values = new ContentValues();
		
		values.put(TOURNAMENT_RANK, score.getTournamentRank());
		values.put(GOOD_ANSWER, score.getGoodAnswer());
		values.put(BAD_ANSWER, score.getBadAnswer());
		values.put(SUCCESS_PERCENTAGE, score.getSuccessPercentage());
		values.put(USER_ID, score.getUserId());
		values.put(LEVEL_ID, score.getLevelId());
		values.put(THEME_ID, score.getThemeId());
		
		return this.db.update(SCORE_TABLE, values, ID + " = " + id, null);
	}
	
	/**
	 * Clear the scores of multiplayer part.
	 * 
	 * @param levelId the score level id.
	 * @return the number of rows affected if a whereClause is passed in, 0 otherwise.
	 * To remove all rows and get a count pass "1" as the whereClause.
	 */
	public int clearMultiplayerScoresPart(long levelId) {
		return this.db.delete(SCORE_TABLE, LEVEL_ID + " = " + levelId, null);
	}
};
